package com.web.new12306.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.web.new12306.dao.AdminDao;
import com.web.new12306.pojo.User;


/**
 * ������
 */
public class AdminDaoImpl implements AdminDao{

	
	Connection conn = null;
	PreparedStatement pstmt = null;
	Statement stmt=null;
	ResultSet rs = null;
	
	
	@Override
	public boolean Login(Connection conn,String name, String pwd) {
		
		boolean flag=false;
		try {
			this.conn=conn;
			String sql = "select * from user where username=? and password=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,name);//
			pstmt.setString(2,pwd);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//this.closeAll(conn, pstmt, rs);
		}
		return flag;
	}

	@Override
	public ArrayList<User> showAll(Connection conn) {
		ArrayList<User> list=new ArrayList<User>();
	   this.conn=conn;
		
		try {
			String sql="select * from user order by id ";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			    while (rs.next()) {
					User user=new User();
					user.setId(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setSex(rs.getString("sex"));
					user.setCardType(rs.getString("cardtype"));
					user.setType(rs.getString("type"));
					list.add(user);
		} 
			    }catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//closeAll(conn, stmt, rs);
		
		
	}return list;
	}
    
	
	@Override
	public int saveUser(Connection conn,User user) {
		int num=0;
		this.conn=conn;
		String sql="insert into user(username,password,sex,cardtype,cardid,type,province,city,realname)" +
				"values(?,?,?,?,?,?,?,?,?)";
		Object []add={user.getUsername(),user.getPassword(),user.getSex(),user.getCardType(),user.getCardId(),user.getType(),user.getProvince(),user.getCity(),user.getRealname()};
		try {
			pstmt=conn.prepareStatement(sql);
			if(pstmt!=null){
				for (int i = 0; i < add.length; i++) {
					pstmt.setObject(i+1, add[i]);
			}
				num=pstmt.executeUpdate();	
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return num;
}

	@Override
	public List<User> queryUsers(Connection conn) {
		// TODO Auto-generated method stub
		this.conn = conn;
		List<User> list = new ArrayList<User>();
		StringBuffer find_sql = new StringBuffer();
		find_sql.append("select * from user");

		User user=null;
		try {
			pstmt = conn.prepareStatement(find_sql.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				 user = new User();
				 user.setId(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setRealname(rs.getString("realname"));
					user.setPassword(rs.getString("password"));
					user.setSex(rs.getString("sex"));
					user.setCardType(rs.getString("cardType"));
					user.setCardId(rs.getString("cardId"));
					user.setProvince(rs.getString("province"));
					user.setCity(rs.getString("city"));
					user.setType(rs.getString("type"));
					
				list.add(user);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}


	@Override
	public List<User> select(Connection conn, Integer page, Integer rows) {
		// TODO Auto-generated method stub
		String sql = "select * from user limit ?,?";
		List<User> list = new ArrayList<User>();
		try {
			this.conn=conn;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, (page-1)*rows);
			pstmt.setInt(2, page*rows);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				   
				user.setUsername(rs.getString("username"));
				user.setRealname(rs.getString("realname"));
				user.setPassword(rs.getString("password"));
				user.setPassword(rs.getString("password2"));
				user.setSex(rs.getString("sex"));
				user.setCardType(rs.getString("cardType"));
				user.setCardId(rs.getString("cardId"));
				user.setEmail(rs.getString("email"));
				user.setProvince(rs.getString("province"));
				user.setCity(rs.getString("city"));
				user.setType(rs.getString("type"));
				list.add(user);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}


	@Override
	public int number(Connection conn) {
		// TODO Auto-generated method stub
		String sql="select count(*) from user";
		int n=0;
		try {
			this.conn=conn;
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				n=rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return n;
	}

	@Override
	//查询所有用户
	public List<User> showAllUser() {
		// TODO Auto-generated method stub
		return null;
	}

}
